package utils;

import java.io.PrintWriter;
import java.sql.Connection;
import java.util.Date;
import java.text.ParseException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import javax.servlet.http.HttpServletRequest;
import org.apache.log4j.Logger;
import org.apache.log4j.PropertyConfigurator;


/**
 * Singleton per il controllo del database
 * @author manuel&simone
 */
public class DBController {
    private static Logger log = Logger.getLogger(DBController.class);
    private static DBController controller = null;
    //private Connection dbConnection = null;
    private JDCConnectionPool pool;
    
    /**
     * Ritorna l'istanza del Singleton DBController (se non esiste già la crea prima)
     * @return the database controller
     */
    public static DBController getInstance(){
        if(controller == null) {
            controller = new DBController();
        }
        return controller;
    }
    
    /**
     * inizializza la classe e il logger
     */
    private DBController(){
        createPool("localhost","default_schema","root","password");
        PropertyConfigurator.configure(DBController.class.getClassLoader().getResource("utils/log4j.properties"));      
    }
    
    /**
     * Connette l'applicazione al database e crea la pool di connessioni (vuota)
     * @param host
     * @param dbName
     * @param user
     * @param password 
     */
    private void createPool(String host, String dbName, String user, String password) {
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            String dbString = "jdbc:mysql://" + host + "/" + dbName;
            pool = new JDCConnectionPool(dbString, user, password);
        }
        catch(Exception e) {
            log.fatal(e.getMessage());
        }
    }
    
    /**
     * Rilascia una connessione in uso
     * @param conn 
     */
    public void releaseConnection(Connection conn) {
        pool.returnConnection((JDCConnection)conn);
    }
    
    /**
     * Tenta la chiusura di ResultSet e Statement e il rilascio della connessione per permetterne l'uso alla prossima richiesta
     * @param drs 
     */
    public void releaseConnection(DBResultSet drs) {
        try {
            drs.getResultSet().close();
            drs.getStatement().close();
            pool.returnConnection((JDCConnection)drs.getConnection());
        } catch(Exception e){
            log.info("Connection not released correctly!");
            log.info(e.getMessage());
        }
    }

    /**
     * Esegue una query data come stringa
     * @param query
     * @return 
     */
    public DBResultSet executeSimpleQuery(String query) throws SQLException {
        Connection conn = pool.getConnection();
        Statement statement = conn.createStatement();
        ResultSet results = statement.executeQuery(query);
        return new DBResultSet(results, statement, conn);
    }
    
    /**
     * Check the existence of a user from given username and password and return it as User objects
     * Verifica l'esistenza di un utente dati username e password
     * @param username
     * @param password
     * @return oggetto di tipo User contenente tutte le informazioni dell'utente, null se non c'è corrispondenza
     */
    public User loginUser(HttpServletRequest request, PrintWriter out, String username, String password, String typeUser) throws SQLException {
        User user = null;
        ResultSet resultSet = null;
        PreparedStatement userLogin = null;
        PreparedStatement userRegister = null;
        Connection conn = pool.getConnection();

        if(typeUser.equals("doctors")) {
            userLogin = conn.prepareStatement("select * from doctors where username=? and password=?");
            userLogin.setString(1, username);
            userLogin.setString(2, password);
            resultSet = userLogin.executeQuery();
        }
        
        if(typeUser.equals("patients")) {
            userLogin = conn.prepareStatement("select * from patients where username=? and password=?");
            userLogin.setString(1, username);
            userLogin.setString(2, password);
            resultSet = userLogin.executeQuery();
        }
        
        if(resultSet.next()){
            user = new User(resultSet.getInt("id"), 
                            resultSet.getString("name"), 
                            resultSet.getString("surname"), 
                            resultSet.getString("username"), 
                            resultSet.getString("password"),
                            typeUser,
                            resultSet.getString("photo"),
                            resultSet.getDate("birthdate"),
                            resultSet.getInt("isRegistered"));
            }
        
        resultSet.close();
        userLogin.close();
        releaseConnection(conn);
        return user;
    }
    
    /**
     * Verifica la disponibilità dello username inserito per un nuovo utente
     * @param username
     * @return 
     */
    public boolean usernameAvailable(String username)  throws SQLException {
        boolean available = true;
        Connection conn = pool.getConnection();
        PreparedStatement userLogin = conn.prepareStatement("select * from doctors where username=?");
        userLogin.setString(1, username);
        ResultSet rs = userLogin.executeQuery();
        if(rs.next()){
            available = false;
        }
        userLogin.close();
        userLogin = conn.prepareStatement("select * from patients where username=?");
        userLogin.setString(1, username);
        rs = userLogin.executeQuery();
        if(rs.next()){
            available = false;
        }
        rs.close();
        userLogin.close();
        releaseConnection(conn);
        return available;
    }
    
    /**
     * 
     * @param name
     * @param surname
     * @param year
     * @param month
     * @param day
     * @param typeUser
     * @param username
     * @param password
     * @param password_cheak
     * @return
     * @throws SQLException 
     */
    public int idRegistration(String name, String surname, String year, String month, String day, String typeUser,String old_username, String new_username, String old_password, String new_password, String new_password_cheak) throws SQLException, ParseException {
        int id = 0;
        String string_date = year + "-" + month + "-" + day;
        Date date = (Date) Utils.convertStringDate(string_date);
        java.sql.Date sqlDate = new java.sql.Date(date.getTime());
        Connection conn = pool.getConnection();
        
        //controllo incrociato OLD_username e OLD_password
        PreparedStatement userLogin = conn.prepareStatement("select * from "+typeUser+" where username=? and password=?");
        PreparedStatement update;
        userLogin.setString(1, old_username);
        userLogin.setString(2, old_password);
        ResultSet rs = userLogin.executeQuery();
        
        //se esiste un user allora si procede
        if(rs.next()) {
            //se va a buon fine controlla il nuovo username
            if(!new_username.equals("")) {
                boolean available = usernameAvailable(new_username);
                if(!available) {
                    //username non disponibile
                    id=2;
                    return id;
                } else if(!new_password.equals(new_password_cheak)) {
                        //nuova password non esatta
                        id=3;
                        return id;
                    } 
            }else {
                        //update
                        if(new_username.equals("") || new_password.equals("")) {
                            update = conn.prepareStatement("Update "+typeUser+" set name=?, surname=?, birthdate=?, username=?, password=?, isRegistered=1 where username=?");
                            update.setString(1, name);
                            update.setString(2, surname);
                            update.setDate  (3, sqlDate);
                            update.setString(4, old_username);
                            update.setString(5, old_password);
                            update.setString(6, old_username);
                        } else {
                            update = conn.prepareStatement("Update "+typeUser+" set name=?, surname=?, birthdate=?, username=?, password=?, isRegistered=1 where username=?");
                            update.setString(1, name);
                            update.setString(2, surname);
                            update.setDate  (3, sqlDate);
                            update.setString(4, new_username);
                            update.setString(5, new_password);
                            update.setString(6, old_username);
                        }
                    int ctrl = update.executeUpdate();
                    update.close();
            
                    releaseConnection(conn);
                    return id;
                    }
            
        } else {
            //non esiste l'utente
            id=1;
            return id;
        }
        
        releaseConnection(conn);
        return id;
    }
    
    /**
     * Ricerca tutte le vaccinazioni di un dato paziente
     * @param patientID
     * @return
     */
    public DBResultSet getVaccinations(int patientID) throws SQLException {
        Connection conn = pool.getConnection();
        PreparedStatement vaccinationStatement = conn.prepareStatement("select * from vaccinations join doctors on vaccinations.id_doctor = doctors.id where id_patient=? order by date desc");
        vaccinationStatement.setInt(1, patientID);
        ResultSet res = vaccinationStatement.executeQuery();
        return new DBResultSet(res, vaccinationStatement, conn);
    }
    
    /**
     * Ricerca tutti i pazienti che non sono stati richiamati negli ultimi X secondi indicati
     * @param seconds
     * @return
     */
    public DBResultSet getPatientsBeforeTime(int seconds) throws SQLException {
        long ts = Calendar.getInstance().getTimeInMillis();
        ts = ts - (1000 * seconds);
        Timestamp t = new Timestamp(ts);
        Connection conn = pool.getConnection();
        PreparedStatement patients = conn.prepareStatement(
                  "(select * from "
                + "(select * from "
                + "(select id, name, surname, birthdate, photo, date "
                + "from patients join vaccinations "
                + "on patients.id = vaccinations.id_patient "
                + "order by date desc) as tbvac "
                + "group by id) as tb1 "
                + "where date < ?) "
                + "union "
                + "(select id, name, surname, birthdate, photo,  null as date "
                + "from patients "
                + "where id not in "
                + "(select distinct id_patient from vaccinations))");
        
        patients.setTimestamp(1, t);
        ResultSet res = patients.executeQuery();
        return new DBResultSet(res, patients, conn);
    }
    
    /**
     * Ottiene le informazioni di una serie di pazienti dati gli id
     * @param ids
     * @return
     */
    public DBResultSet getPatientsInfo(ArrayList<String> ids) throws SQLException {
        String idList = "(";
        String tmp;
        int cnt = 0;
        for(int i=0;i<ids.size();i++) {
            tmp = ids.get(i);
            if(Utils.tryParseInt(tmp)){
                idList += tmp + ",";
                cnt++;
            }
        }
        if(cnt == 0) {
            return null;
        }
        
        idList = idList.substring(0, idList.length()-1);
        idList += ")";
        
        Connection conn = pool.getConnection();
        Statement patients = conn.createStatement();
        ResultSet res = patients.executeQuery("select * from patients where id in " + idList);
        return new DBResultSet(res, patients, conn);
    }
    
    /**
     * Ottiene le informazioni di un paziente dato l'id
     * @param id
     * @return
     */
    public DBResultSet getPatientInfo(int id) throws SQLException{
        Connection conn = pool.getConnection();
        PreparedStatement patient = conn.prepareStatement("select * from patients where id=?");
        patient.setInt(1,id);
        ResultSet res = patient.executeQuery();
        return new DBResultSet(res, patient, conn);
    }
    
    /**
     * Registra le vaccinazioni di un dottore sui pazienti specificati dati gli id
     * @param doctorId
     * @param patientIds
     * @return
     */
    public boolean registerVaccinations(int doctorId, ArrayList<String> patientIds, Timestamp date) throws SQLException {
        Calendar cal = Calendar.getInstance();
        int cnt = 0;
        String query = "insert into vaccinations (id_doctor, id_patient, date) values ";
        
        String tmp;
        for(int i=0;i<patientIds.size();i++) {
            tmp = patientIds.get(i);
            if(Utils.tryParseInt(tmp)){
                query += "(" + doctorId + "," + Integer.parseInt(tmp) + ",?),";
                cnt++;
            }
        }
        if(cnt == 0) {
            return true;
        } 
        // nothing to add
        else {
            query = query.substring(0, query.length()-1);
        }
        
        boolean res = false;
        Connection conn = pool.getConnection();
        PreparedStatement vaccination = conn.prepareStatement(query);
        for(int i=1;i <= cnt;i++) {
            vaccination.setTimestamp(i, date);
        }
        if(vaccination.executeUpdate()>0) {
            res = true;
        }
        vaccination.close();
        releaseConnection(conn);
        return res;
    }
    
    /**
     * Ottiene i dati dei pazienti a cui sono state effettuate vaccinazioni negli ultimi X secondi indicati
     * @param seconds
     * @return
     */
    public DBResultSet getPatientsAfterTime(int seconds) throws SQLException {
        long ts = Calendar.getInstance().getTimeInMillis();
        ts = ts - (1000 * (long)seconds);
        Timestamp t = new Timestamp(ts); // ora riferimento utente
        
        Connection conn = pool.getConnection();
        PreparedStatement vaccinations = conn.prepareStatement("select * from "
                + "(select * from "
                + "(select patients.id, patients.name, patients.surname, patients.birthdate, "
                + "patients.photo, date, doctors.name as docname, doctors.surname as docsurname "
                + "from patients, vaccinations, doctors "
                + "where patients.id=vaccinations.id_patient and  vaccinations.id_doctor = doctors.id "
                + "order by date desc) as tbvac group by id) "
                + "as tmp_t where date > ?");
        vaccinations.setTimestamp(1, t);
        ResultSet res = vaccinations.executeQuery();
        return new DBResultSet(res, vaccinations, conn);
    }
    
    /**
     * Ottiene l'elenco dei pazienti
     * @return
     */
    public DBResultSet getPatients() throws SQLException {
        Connection conn = pool.getConnection();
        Statement patients = conn.createStatement();
        ResultSet res = patients.executeQuery("select id from patients");
        return new DBResultSet(res, patients, conn);
    }

    
}
